package agenceVoyageVirtuel.server.base;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import agenceVoyageVirtuel.client.beans.CommandeData;
import agenceVoyageVirtuel.client.beans.SouvenirData;
import agenceVoyageVirtuel.server.DB_Conn;

public class DB_Souvenir extends DB_Conn {
	/**
	 * constructor - nothing to do
	 */
	public DB_Souvenir() {
		// nothing to do
	}
	
	public List<SouvenirData> getSouvenirs(int idCommande) {
		String query = "SELECT t_souvenir.idSouvenir, libSouvenir, descriptionSouvenir, tarifSouvenir"
				+ " FROM t_souvenirs JOIN t_souvenir"
				+ " WHERE idCommande=" + idCommande
				+ " AND t_souvenirs.idSouvenir=t_souvenir.idSouvenir;";

		// prepare for rpc transport
		List<SouvenirData> souvenirDatas = new ArrayList<SouvenirData>();
		
		try {
			Connection connection = getConn();
			Statement select = connection.createStatement();
			ResultSet result = select.executeQuery(query);
			int i = 0;
			
			while (result.next()) {
				souvenirDatas.add(new SouvenirData());
	
				souvenirDatas.get(i).setIdSouvenir(result.getInt(1));
				souvenirDatas.get(i).setLibSouvenir(result.getString(2));
				souvenirDatas.get(i).setDescriptionSouvenir(result.getString(3));
				souvenirDatas.get(i).setTarifSouvenir(result.getInt(4));
	
				i++;
			}
	
			// clean up
			result.close();
			connection.close();
		} catch(Exception e) {
			System.err.println("Mysql Statement Error: " + query);
			e.printStackTrace();
		}
	
		// return datas
		return souvenirDatas;			
	}	
	
	public List<SouvenirData> getSouvenirs() {
		String query = "SELECT idSouvenir, libSouvenir, descriptionSouvenir, tarifSouvenir FROM t_souvenir;";

		// prepare for rpc transport
		List<SouvenirData> souvenirDatas = new ArrayList<SouvenirData>();
		
		try {
			Connection connection = getConn();
			Statement select = connection.createStatement();
			ResultSet result = select.executeQuery(query);
			int i = 0;
			
			while (result.next()) {
				souvenirDatas.add(new SouvenirData());
				souvenirDatas.get(i).setIdSouvenir(result.getInt(1));
				souvenirDatas.get(i).setLibSouvenir(result.getString(2));
				souvenirDatas.get(i).setDescriptionSouvenir(result.getString(3));
				souvenirDatas.get(i).setTarifSouvenir(result.getInt(4));
				
				i++;
			}

			// clean up
			result.close();
			connection.close();
		} catch(Exception e) {
			System.err.println("Mysql Statement Error: " + query);
			e.printStackTrace();
		}

		// return datas
		return souvenirDatas;		
	}
	
	public void commanderSouvenirs(CommandeData commandeEnCours) {
		for(SouvenirData souvenir : commandeEnCours.getSouvenirs()) {
			String query = "INSERT INTO t_souvenirs (idCommande, idSouvenir)"
					+ " VALUES (" + commandeEnCours.getIdCommande()
					+ ", " + souvenir.getIdSouvenir()
					+ ");";
		
			try {
				Connection connection = getConn();
				Statement insert = connection.createStatement();
				
	            insert.executeUpdate(query);
	            
	            insert.close();
				connection.close();
			} catch(Exception e) {
				System.err.println("Mysql Statement Error: " + query);
				e.printStackTrace();
			}						
		}
	}

	private int chercherSouvenir(String nouveauSouvenir, String descriptionSouvenir) {
		String query = "SELECT idSouvenir"
				+ " FROM t_souvenir"
				+ " WHERE libSouvenir=" + nouveauSouvenir
				+ " OR descriptionSouvenir=" + descriptionSouvenir
				+ ";";
	
		// prepare for rpc transport
		int id = -1;
		
		try {
			Connection connection = getConn();
			Statement select = connection.createStatement();
			ResultSet result = select.executeQuery(query);
			
			while(result.next()) {
				id = result.getInt(1); //idSouvenir
				break;
			}
	
			// clean up
			result.close();
			connection.close();
		} catch(Exception e) {
			System.err.println("Mysql Statement Error: " + query);
			e.printStackTrace();
		}
	
		// return datas
		return id;		
	}
	
	public void creerSouvenir(String nouveauSouvenir, String descriptionSouvenir, int tarifSouvenir) {
		int id = chercherSouvenir(nouveauSouvenir, descriptionSouvenir);
		
		if(id != -1)
			return;
		
		String query = "INSERT INTO t_souvenir (libSouvenir, descriptionSouvenir, tarifSouvenir)"
				+ " VALUES (" + nouveauSouvenir
				+ ", " + descriptionSouvenir
				+ ", " + tarifSouvenir
				+ ");";
	
		try {
			Connection connection = getConn();
			Statement  insert = connection.createStatement();
			insert.executeUpdate(query);
			
	        insert.close();
			connection.close();
		} catch(Exception e) {
			System.err.println("Mysql PrepareStatement Error: " + query);
			e.printStackTrace();
		}
		
		return;		
	}	
}
